package com.bright.ghj.common.util.excel;

import com.alibaba.excel.EasyExcel;
import com.alibaba.excel.ExcelReader;
import com.alibaba.excel.ExcelWriter;
import com.alibaba.excel.read.builder.ExcelReaderBuilder;
import com.alibaba.excel.read.builder.ExcelReaderSheetBuilder;
import com.alibaba.excel.read.metadata.ReadSheet;
import com.alibaba.excel.write.metadata.WriteSheet;
import com.alibaba.excel.write.metadata.fill.FillConfig;
import com.bright.ghj.common.pojo.bo.ExcelReadFileItem;
import com.bright.ghj.common.util.ListUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.nio.file.Files;
import java.util.*;

/**
 * @Author hxj
 * @Date 2024/1/11 17:05
 * @Description
 */
@Slf4j
public class ExcelUtil {


    /**
     *
     * @param inputStream
     * @param startRow
     * @return
     * @throws IOException
     */
    public static List<Map<String, Object>> readExcelFromFile(InputStream inputStream, int sheetNo, String sheetName, int startRow, boolean ignoreEmptyRow) throws IOException {
        return readExcelFromFile(inputStream, sheetNo, sheetName, startRow, ignoreEmptyRow, null);
    }

    public static List<Map<String, Object>> readExcelFromFile(InputStream inputStream, int sheetNo, String sheetName, int startRow, boolean ignoreEmptyRow, ExcelReadListener readListener) throws IOException {
        ExcelReadListener listener;
        if (readListener != null) {
            listener = readListener;
        } else {
            listener = new ExcelReadListener();
        }
        getExcelReaderSheetBuilder(EasyExcel.read(inputStream, listener), sheetNo, sheetName, startRow, ignoreEmptyRow).doRead();
        return listener.getMapListData();
    }

//    public static List<Map<String, Object>> readExcelFromFile(File file) throws IOException {
//        return readExcelFromFile(file, null);
//    }

    public static List<Map<String, Object>> readExcelFromFile(File file, ExcelReadListener excelReadListener) throws IOException {
        try (InputStream inputStream = Files.newInputStream(file.toPath())) {
            return readExcelFromFile(inputStream, -1, null, 1, true, excelReadListener);
        }
    }

    private static ExcelReaderSheetBuilder getExcelReaderSheetBuilder(ExcelReaderBuilder builder, int sheetNo, String sheetName, int startRow, boolean ignoreEmptyRow) {
//        ExcelReaderBuilder builder = EasyExcel.read(inputStream);
        builder = builder.ignoreEmptyRow(ignoreEmptyRow);
        builder = builder.headRowNumber(startRow);
        ExcelReaderSheetBuilder sheet;
        if (sheetNo != -1 && sheetName != null) {
            sheet = builder.sheet(sheetNo, sheetName);
        } else if (sheetNo != -1) {
            sheet = builder.sheet(sheetNo);
        } else if (sheetName != null) {
            sheet = builder.sheet(sheetName);
        } else {
            sheet = builder.sheet();
        }

        return sheet;
    }


    public static List<Map<String, Object>> readExcelFromWeb(MultipartFile file, int sheetNo, int startRow, ExcelReadListener excelReadListener) throws IOException {
        return readExcelFromFile(file.getInputStream(), sheetNo, null, startRow, true, excelReadListener);
    }

    public static List<Map<String, Object>> readExcelFromFile(File file, int sheetNo, int startRow, ExcelReadListener excelReadListener) throws IOException {
        return readExcelFromFile(Files.newInputStream(file.toPath()), sheetNo, null, startRow, true, excelReadListener);
    }

    public static List<Map<String, Object>> readExcelFromWeb(MultipartFile file, String sheetName, int startRow, ExcelReadListener excelReadListener) throws IOException {
        return readExcelFromFile(file.getInputStream(), -1, sheetName, startRow, true, excelReadListener);
    }

    /**
     * 获取工作所有工作表sheetName
     * @param inputStream
     * @return
     */
    public static List<String> getSheetNames(InputStream inputStream) {
        try (ExcelReader reader = EasyExcel.read(inputStream).build()) {
            List<ReadSheet> readSheets = reader.excelExecutor().sheetList();
            return ListUtil.toSingleList(readSheets, ReadSheet::getSheetName);
        }
    }

    /**
     * 获取工作所有工作表sheetName
     * @param file
     * @return
     */
    public static List<String> getSheetNames(File file) throws IOException {
        return getSheetNames(Files.newInputStream(file.toPath()));
    }


    /**
     * 按模板填充 多工作表sheet情况
     * @param outputStream
     * @param templateFileName
     * @param sheetsListData 各个工作表的列表型数据 key=sheetNo或sheetName value=数据
     * @param sheetsParamData 各个工作表的非列表型数据 key=sheetNo或sheetName value=数据
     * @throws IOException
     */
    public static void writeExcelToWebByTemplate(OutputStream outputStream, String templateFileName, Map<Object, List<Map<String, Object>>> sheetsListData, Map<Object, Map<String, Object>> sheetsParamData) throws IOException {

        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
             ExcelWriter excelWriter = EasyExcel.write(out)
                     .withTemplate(templateFileName)
                     .registerConverter(new TimestampConverter())
                     .registerConverter(new DateConverter())
                     .build()) {


            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();

            // 循环每一个工作表sheet
            for (Object sheetKey : sheetsListData.keySet()) {
                WriteSheet writeSheet;
                if (sheetKey instanceof Integer) {
                    writeSheet = EasyExcel.writerSheet().sheetNo((Integer) sheetKey).build();
                } else {
                    writeSheet = EasyExcel.writerSheet().sheetName((String) sheetKey).build();
                }
                excelWriter.fill(sheetsListData.get(sheetKey), fillConfig, writeSheet);
                excelWriter.fill(sheetsParamData.get(sheetKey), writeSheet);
            }
            excelWriter.finish();

            byte[] content = out.toByteArray();
            outputStream.write(content);
            outputStream.close();
        }
    }

    /**
     * 按模板填充 单工作表sheet的情况
     * @param outputStream
     * @param templateFileName
     * @param listData
     * @param otherParamData
     * @throws IOException
     */
    public static void writeExcelToWebByTemplate(OutputStream outputStream, String templateFileName, List<Map<String, Object>> listData, Map<String, Object> otherParamData) throws IOException {
        Map<Object, List<Map<String, Object>>> sheetsListData = new HashMap<>();
        // 0表示第一个工作表
        sheetsListData.put(0, listData);
        Map<Object, Map<String, Object>> sheetsParamData = new HashMap<>();
        sheetsParamData.put(0, otherParamData);

        writeExcelToWebByTemplate(outputStream, templateFileName, sheetsListData, sheetsParamData);
    }


    /**
     * 按模板填充 多工作表sheet情况
     * @param outputStream
     * @param sheetsListData 各个工作表的列表型数据 key=sheetNo或sheetName value=数据
     * @param sheetsParamData 各个工作表的非列表型数据 key=sheetNo或sheetName value=数据
     * @throws IOException
     */
    public static void writeExcelToWeb(OutputStream outputStream, Map<String, List<ExcelReadFileItem>> titles, Map<String, List<Map<String, Object>>> sheetsListData, Map<String, Map<String, Object>> sheetsParamData) throws IOException {
        Map<String, List<List<String>>> head = new HashMap<>();
        Map<String, List<String>> fieldNames = new HashMap<>();
        for (String sheetKey : sheetsListData.keySet()) {
            List<List<String>> eachHead = new ArrayList<>();
            List<String> eachFieldNames = new ArrayList<>();
            for (ExcelReadFileItem fileItem : titles.get(sheetKey)) {
                List<String> title = new ArrayList<>();
                title.add(fileItem.getFieldDoc());
                eachHead.add(title);
                eachFieldNames.add(fileItem.getFieldName());
            }
            head.put(sheetKey, eachHead);
            fieldNames.put(sheetKey, eachFieldNames);
        }

        writeExcelToWeb(outputStream, head, fieldNames, sheetsListData, sheetsParamData);
    }

    public static void writeExcelToWeb(OutputStream outputStream, Map<String, List<List<String>>> head, Map<String, List<String>> fieldNames, Map<String, List<Map<String, Object>>> sheetsListData, Map<String, Map<String, Object>> sheetsParamData) throws IOException {

        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
             ExcelWriter excelWriter = EasyExcel.write(out)
                     .registerConverter(new TimestampConverter())
                     .registerConverter(new DateConverter())
                     .build()) {

            // 循环每一个工作表sheet
            for (String sheetKey : sheetsListData.keySet()) {

                List<List<Object>> excelData = new ArrayList<>();
                for (Map<String, Object> listDatum : sheetsListData.get(sheetKey)) {
                    List<Object> excelLine = new ArrayList<>();
                    for (String fieldName : fieldNames.get(sheetKey)) {
                        Object obj = listDatum.get(fieldName);
                        excelLine.add(obj);
                    }
                    excelData.add(excelLine);
                }

//                List<List<String>> head = new ArrayList<>();
//                for (ExcelReadFileItem fileItem : titles.get(sheetKey)) {
//                    List<String> title = new ArrayList<>();
//                    title.add(fileItem.getFieldDoc());
//                    head.add(title);
//                }

                WriteSheet writeSheet = EasyExcel.writerSheet().sheetName(sheetKey)
                            .head(head.get(sheetKey))
                            .registerConverter(new TimestampConverter())
                            .registerConverter(new DateConverter())
                            .build();

                excelWriter.write(excelData, writeSheet);

            }
            excelWriter.finish();

            byte[] content = out.toByteArray();
            outputStream.write(content);
            outputStream.close();
        }
    }


    /**
     * 按模板填充 多工作表sheet情况
     */
    public static void writeExcelToFile(String outputFileName, List<String> headNames, List<Map<String, Object>> listData, Map<Object, Map<String, Object>> sheetsParamData) throws IOException {

        List<List<Object>> excelData = new ArrayList<>();
        for (Map<String, Object> listDatum : listData) {
            List<Object> excelLine = new ArrayList<>();
            for (String headName : headNames) {
                Object obj = listDatum.get(headName);
                excelLine.add(obj);
            }
            excelData.add(excelLine);
        }
        
        List<List<String>> head = new ArrayList<>();
        for (String headName : headNames) {
            List<String> title = new ArrayList<>();
            title.add(headName);
            head.add(title);
        }


        try (ExcelWriter excelWriter = EasyExcel.write(outputFileName).build()) {

            WriteSheet writeSheet = EasyExcel.writerSheet("模板")
                    .head(head)
//                    .relativeHeadRowIndex(startRow)
                    .registerConverter(new TimestampConverter())
                    .registerConverter(new DateConverter())
                    .build();

            excelWriter.write(excelData, writeSheet);

        } catch (Throwable throwable) {
            throwable.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
//        String fileName = "D:\\hxj\\test\\cwgk\\template3.xls";
//        String templateFileName = "D:\\hxj\\test\\cwgk\\test2.xls";
//        List<Map<Integer, String>> data = readExcelFromFile(fileName);
//        System.out.println("开始休眠");
//        Thread.sleep(5000);
//        System.out.println("a:" + data);
//        System.out.println("tableName:" + data.get(0).get(0));
//        System.out.println(":");

//        int i = 50;
//        System.out.println(((double)i)/490*2000);

//        BigDecimal num = new BigDecimal("2023.00");
//        System.out.println(Integer.parseInt(num.toString()));
//        System.out.println(Integer.valueOf(num.toString()));

//        System.out.println("b:" + EasyExcel.read(fileName).sheet(1).headRowNumber(0).doReadSync());
//        EasyExcel.writerSheet(0).doFill();

//        OutputStream outputStream = Files.newOutputStream(Paths.get(fileName));
//        String jsonString = "[{\"c1\":\"负债\",\"c2\":1,\"c3\":100,\"c4\":200},{\"c1\":\"收益\",\"c2\":2,\"c3\":300.5,\"c4\":420.23}]";
//        ObjectMapper objectMapper = new ObjectMapper();
//        List<Map<String, Object>> listData;
//        try {
//            listData = objectMapper.readValue(jsonString, List.class);
//        } catch (JsonProcessingException e) {
//            throw new RuntimeException(e);
//        }
//
//        Map<String, Object> otherParamData = new HashMap<>();
//        otherParamData.put("tbdw", "石东联社");
//        otherParamData.put("date", "2024-01-17");
//        otherParamData.put("footer1", "罗辑");
//        otherParamData.put("footer2", "叶文洁");
//        otherParamData.put("footer3", "吴悦");
//        otherParamData.put("footer4", "庄严");


//        try {
//            ExcelUtil.writeExcelToWebByTemplate(outputStream, templateFileName, listData, otherParamData);
//        } catch (IOException e) {
//            throw new RuntimeException(e);
//        }

//        EasyExcel.read(templateFileName, new ReadListener() {
//            @Override
//            public void invoke(Object data, AnalysisContext context) {
//                Map<Integer, Cell> cellMap = context.readRowHolder().getCellMap();
//                for (Map.Entry<Integer, Cell> entry : cellMap.entrySet()) {
//                    System.out.println(entry.getKey() + ", " + entry.getValue());
//                }
//            }
//
//            @Override
//            public void doAfterAllAnalysed(AnalysisContext context) {
//
//            }
//        }).sheet(0).doRead();

//        try (ByteArrayOutputStream out = new ByteArrayOutputStream();
//             ExcelWriter excelWriter = EasyExcel.write(outputStream).withTemplate(templateFileName).build()) {
//            WriteSheet writeSheet = EasyExcel.writerSheet(0).build(); // 这里加sheetNo可以指定写到哪个sheet
//            // 这里注意 入参用了forceNewRow 代表在写入list的时候不管list下面有没有空行 都会创建一行，然后下面的数据往后移动。默认 是false，会直接使用下一行，如果没有则创建。
//            // forceNewRow 如果设置了true,有个缺点 就是他会把所有的数据都放到内存了，所以慎用
//            // 简单的说 如果你的模板有list,且list不是最后一行，下面还有数据需要填充 就必须设置 forceNewRow=true 但是这个就会把所有数据放到内存 会很耗内存
//            FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//            excelWriter.fill(listData, fillConfig, writeSheet);
//            excelWriter.fill(otherParamData, writeSheet);
//            excelWriter.finish();
//
//            byte[] content = out.toByteArray();
//            outputStream.write(content);
//            outputStream.close();
//        }
//        ExcelWriter excelWriter = EasyExcel.write(fileName).build();
//        WriteSheet writeSheet = EasyExcel.write(fileName).withTemplate(templateFileName).sheet("表1 财务收支预算表").build();


//        ReadSheet build = EasyExcel.read(fileName).sheet(0).build();
//        excelWriter.fill()


//        FillConfig fillConfig = FillConfig.builder().forceNewRow(Boolean.TRUE).build();
//        excelWriter.write(listData, writeSheet);
////        excelWriter.fill(otherParamData, writeSheet);
//        excelWriter.finish();

//        EasyExcel.write(fileName).withTemplate(templateFileName).sheet(0).doFill(listData);
//        String outputFileName = "D:\\hxj\\test\\cwgk\\test4.xls";
//        ExcelUtil.writeExcelToFile(outputFileName, Arrays.asList("c1", "c2", "c3", "c4"), listData, new HashMap<>());
        String name = "123321";
        System.out.println(Base64.getEncoder().encodeToString(name.getBytes("ASCII")));
        String ascii = Base64.getEncoder().encodeToString(name.getBytes("ASCII"));
        System.out.println(ascii);

    }
}
